<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Major extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "major";
		$this->prefix = "maj";
	}

	function getMajor($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "majName", $dir = "ASC") {

		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "majName"));
		$dir = Common::coalesce(array($dir, "ASC"));

		$qs =	"
					select
						maj_id, maj_name, maj_url_name, maj_prog_type, maj_prog_type2, maj_presenter, maj_list_from_date, maj_list_till_date, ins_id, ins_name, ins_url_name, city_name, city_url_name, cou_name, cou_url_name, con_name, con_url_name, maj_sub_id, sub_name, type.szo_lname as type_szo_lname, type2.szo_lname as type2_szo_lname, t.e2c_temp_flag,
						description.e2c_text as description,
						group_concat(distinct(ifnull(acc_id, '')) separator ',') as accList
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = ins_id) and u2e_page_cat = 'MAJ' "):(""))."

						left join ".$this->db->dbprefix."szotar as type on maj_prog_type = type.szo_sname and type.szo_type = 'PROGTYPE' and type.szo_del = 0
						left join ".$this->db->dbprefix."szotar as type2 on maj_prog_type2 = type2.szo_sname and type2.szo_type = 'PROGTYPE_2' and type2.szo_del = 0
						left join (
							select
								e2c_ent_id, e2c_temp_flag
							from
								".$this->db->dbprefix."ent2cat 
							where
								e2c_entity = 'MAJ' and e2c_temp_flag = 'I' and e2c_del = 0
							group by
								e2c_ent_id
						) as t on t.e2c_ent_id = maj_id
						
						left join ".$this->db->dbprefix."ent2cat as description on maj_id = description.e2c_ent_id and description.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJDescriptionId"))."' and description.e2c_entity = 'MAJ' and description.e2c_del = 0 ".
						//left join ".$this->db->dbprefix."category as description_cat on description_cat.cat_id = description.e2c_cat_id and description_cat.cat_entity = 'MAJ' and description_cat.cat_name = '".$this->db->escape_str($this->config->item("categoryDescription"))."' and description_cat.cat_del = 0 
						
						"left join ".$this->db->dbprefix."ent2cat as grade_level on maj_id = grade_level.e2c_ent_id and grade_level.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJTrainingLevelId"))."' and grade_level.e2c_entity = 'MAJ' and grade_level.e2c_del = 0 ".
						//left join ".$this->db->dbprefix."category as grade_level_cat on grade_level_cat.cat_id = grade_level.e2c_cat_id and grade_level_cat.cat_entity = 'MAJ' and grade_level_cat.cat_name = '".$this->db->escape_str($this->config->item("categoryGradeLevel"))."' and grade_level_cat.cat_del = 0 
						
						"left join ".$this->db->dbprefix."ent2cat as language on maj_id = language.e2c_ent_id and language.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJLanguageId"))."' and  language.e2c_entity = 'MAJ' and language.e2c_del = 0 ".
						//left join ".$this->db->dbprefix."category as language_cat on language_cat.cat_id = language.e2c_cat_id and language_cat.cat_entity = 'MAJ' and language_cat.cat_name = '".$this->db->escape_str($this->config->item("categoryLanguage"))."' and language_cat.cat_del = 0 
						
						"left join ".$this->db->dbprefix."acc2ins_maj on maj_id = a2im_maj_id and a2im_del = 0 ".
						"left join ".$this->db->dbprefix."accomodation on acc_id = a2im_acc_id and acc_del = 0 ".
						
					"where
						maj_del = 0 ".((($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						maj_id
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
						";
						/* 2012.12.06.
					group by
						ins_id
						*/
		$result = $this->db->query($qs);
		//Common::pr($qs);

		$getMajor = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getMajor[] = array(
					"majId" => $row->maj_id,
					"majName" => $row->maj_name,
					"majUrlName" => $row->maj_url_name,
					"majInsId" => $row->ins_id,
					"insName" => $row->ins_name,
					"insUrlName" => $row->ins_url_name,
					"cityName" => $row->city_name,
					"cityUrlName" => $row->city_url_name,
					"couName" => $row->cou_name,
					"couUrlName" => $row->cou_url_name,
					"conName" => $row->con_name,
					"conUrlName" => $row->con_url_name,
					"subId" => $row->maj_sub_id,
					"subName" => $row->sub_name,
					"majProgType" => $row->maj_prog_type,
					"majProgTypeLname" => $row->type_szo_lname,
					"majProgType2" => $row->maj_prog_type2,
					"majProgType2Lname" => $row->type2_szo_lname,
					"e2cTempFlag" => $row->e2c_temp_flag,
					"e2cDescription" => $row->description,
					"majPresenter" => $row->maj_presenter,
					"majListFromDate" => Common::dateFromDatabase($row->maj_list_from_date),
					"majListTillDate" => Common::dateFromDatabase($row->maj_list_till_date),
					"accList" => $row->accList,
				);
			}
		}

		return $getMajor;
	}

	function getMajorCount($where = NULL, $fields = NULL, $query = NULL) {

		$fieldsQuery = ((!is_null($fields))?Common::fieldsquery($fields, $query):(""));

		$qs =	"
					select
						count(*) as cnt
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0

						".((in_array($this->uri->segment(1), array("admin", "admin_lte")) and Common::isAdmin())?
						(" join ".$this->db->dbprefix."usr2ent on u2e_del = 0 and u2e_usr_id = '".$this->session->userdata("usrId")."' and (u2e_ins_id = ins_id) and u2e_page_cat = 'MAJ' "):(""))."

						left join ".$this->db->dbprefix."ent2cat as description on maj_id = description.e2c_ent_id and description.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJDescriptionId"))."' and description.e2c_entity = 'MAJ' and description.e2c_del = 0

						left join ".$this->db->dbprefix."ent2cat as grade_level on maj_id = grade_level.e2c_ent_id and grade_level.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJTrainingLevelId"))."' and grade_level.e2c_entity = 'MAJ' and grade_level.e2c_del = 0

						left join ".$this->db->dbprefix."ent2cat as language on maj_id = language.e2c_ent_id and language.e2c_cat_id = '".$this->db->escape_str($this->config->item("categoryMAJLanguageId"))."' and  language.e2c_entity = 'MAJ' and language.e2c_del = 0

					where
						maj_del = 0 ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					";
		$result = $this->db->query($qs);

		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				return $row->cnt;
			}
		}
	}

	function getProgType($where = NULL) {
		$qs =	"
					select
						szo_sname, szo_lname, szo_url_name
					from
						".$this->db->dbprefix."szotar 
						left join ".$this->db->dbprefix.$this->tableName." on maj_prog_type = szo_sname and maj_del = 0
						left join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						left join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						left join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
					where
						szo_type = 'PROGTYPE' and szo_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						szo_sname
					order by
						szo_lname
					";
		//Common::pr($qs);
		$result = $this->db->query($qs);

		$getProgType = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getProgType[$row->szo_sname]["szoLname"] = $row->szo_lname;
				$getProgType[$row->szo_sname]["szoUrlName"] = $row->szo_url_name;
			}
		}
		//Common::pr($getProgType);
		return $getProgType;
	}

	function getAvailablePrograms($where = NULL, $start = 0, $limit = NULL) {
		if(is_null($limit)) {
			$limit = $this->config->item("availableProgramPageSize");
		}
		$qs =	"
					select
						SQL_CALC_FOUND_ROWS sub_id, sub_name
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."szotar on maj_prog_type = szo_sname and szo_type = 'PROGTYPE' and szo_del = 0
						join ".$this->db->dbprefix."institute as kar on kar.ins_id = maj_ins_id and kar.ins_del = 0
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."ent2cat on maj_id = e2c_ent_id and e2c_ent_id = maj_id and coalesce(e2c_int, e2c_text) is not null and e2c_del = 0
					where
						maj_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						sub_id
					order by
						sub_name
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
					//Common::pr($qs);
		$result = $this->db->query($qs);

		$getAvailablePrograms = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getAvailablePrograms["sub"][$row->sub_id]["subName"] = $row->sub_name;
			}

			$result = $this->db->query("SELECT 2, FOUND_ROWS() as cnt")->result_array();  
			$getAvailablePrograms["cnt"] = $result[0]["cnt"];

			$qs =	"
						select
							sub_id, maj_id, maj_name, maj_url_name, maj_prog_type, szo_lname, coalesce(iin.ins_name, kar.ins_name) as ins_name, coalesce(kar.ins_url_name, iin.ins_url_name) as ins_url_name, city_url_name, cou_url_name, con_url_name, szo_lname, cat_id, cat_name, cat_highlight, e2c_int, e2c_text
						from
							".$this->db->dbprefix.$this->tableName."
							join ".$this->db->dbprefix."szotar on maj_prog_type = szo_sname and szo_type = 'PROGTYPE' and szo_del = 0
							join ".$this->db->dbprefix."institute as kar on kar.ins_id = maj_ins_id and kar.ins_del = 0
							left join ".$this->db->dbprefix."institute as iin on iin.ins_id = kar.ins_ins_id and iin.ins_del = 0
							join ".$this->db->dbprefix."city on city_id = coalesce(iin.ins_city_id, kar.ins_city_id) and city_del = 0
							join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
							join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
							join ".$this->db->dbprefix."category on cat_del = 0 and cat_type != 'T' and cat_entity = 'MAJ'
							left join ".$this->db->dbprefix."ent2cat on maj_id = e2c_ent_id and cat_id = e2c_cat_id and e2c_ent_id = maj_id and e2c_entity = 'MAJ' and coalesce(e2c_int, e2c_text) is not null and e2c_del = 0
						where
							maj_del = 0 and maj_sub_id in (".implode(",", array_keys($getAvailablePrograms["sub"])).") ".((!is_null($where))?(" and ".$where):(""))."
						order by
							sub_name, cat_order
						";
			$result = $this->db->query($qs);

			if($result->num_rows()) {
				foreach ($result->result() as $row) {
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["majName"] = $row->maj_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["majProgType"] = $row->maj_prog_type;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["majProgTypeLname"] = $row->szo_lname;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["majUrlName"] = $row->maj_url_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["insUrlName"] = $row->ins_url_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["cityUrlName"] = $row->city_url_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["couUrlName"] = $row->cou_url_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["conUrlName"] = $row->con_url_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["insName"] = $row->ins_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["catName"] = $row->cat_name;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["catHighlight"] = $row->cat_highlight;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["e2cInt"] = $row->e2c_int;
					$getAvailablePrograms["sub"][$row->sub_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["e2cText"] = $row->e2c_text;
				}
			}

		}

		//Common::pr($getAvailablePrograms);
		return $getAvailablePrograms;
	}

	function getAvailableProgramsBySubject($where = NULL, $start = 0, $limit = NULL, $groupBy = NULL) {
		if(is_null($limit)) {
			$limit = $this->config->item("availableProgramPageSize");
		}
		$qs =	"
					select
						SQL_CALC_FOUND_ROWS ins_id, ins_name, city_name, cou_name, ins_url_name, city_url_name, cou_url_name, con_url_name
					from
						".$this->db->dbprefix.$this->tableName."
						join ".$this->db->dbprefix."szotar on maj_prog_type = szo_sname and szo_type = 'PROGTYPE' and szo_del = 0
						join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
						join ".$this->db->dbprefix."category on cat_del = 0 and cat_type != 'T' and cat_entity = 'MAJ'
						join ".$this->db->dbprefix."ent2cat on maj_id = e2c_ent_id and cat_id = e2c_cat_id and e2c_entity = 'MAJ' and coalesce(e2c_int, e2c_text) is not null and e2c_del = 0
					where
						maj_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
					group by
						ins_id ".((!is_null($groupBy))?(", ".$groupBy):(""))."
					order by
						ins_name
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
		$result = $this->db->query($qs);

		$getAvailablePrograms = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {
				$getAvailablePrograms["ins"][$row->ins_id]["insName"] = $row->ins_name;
				$getAvailablePrograms["ins"][$row->ins_id]["cityName"] = $row->city_name;
				$getAvailablePrograms["ins"][$row->ins_id]["couName"] = $row->cou_name;
				$getAvailablePrograms["ins"][$row->ins_id]["insUrlName"] = $row->ins_url_name;
				$getAvailablePrograms["ins"][$row->ins_id]["cityUrlName"] = $row->city_url_name;
				$getAvailablePrograms["ins"][$row->ins_id]["couUrlName"] = $row->cou_url_name;
				$getAvailablePrograms["ins"][$row->ins_id]["conUrlName"] = $row->con_url_name;
			}

			$result = $this->db->query("SELECT FOUND_ROWS() as cnt")->result_array();  
			$getAvailablePrograms["cnt"] = $result[0]["cnt"];

			$qs =	"
						select
							sub_id, maj_id, maj_name, maj_url_name, maj_prog_type, szo_lname, ins_id, ins_name, ins_url_name, city_url_name, cou_url_name, con_url_name, szo_lname, cat_id, cat_name, cat_highlight, e2c_int, e2c_text
						from
							".$this->db->dbprefix.$this->tableName."
							join ".$this->db->dbprefix."szotar on maj_prog_type = szo_sname and szo_type = 'PROGTYPE' and szo_del = 0
							join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0 and ins_id in (".implode(",", array_keys($getAvailablePrograms["ins"])).")
							join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
							join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
							join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
							join ".$this->db->dbprefix."subject on sub_id = maj_sub_id and sub_del = 0
							join ".$this->db->dbprefix."category on cat_del = 0 and cat_type != 'T' and cat_entity = 'MAJ'
							join ".$this->db->dbprefix."ent2cat on maj_id = e2c_ent_id and cat_id = e2c_cat_id and e2c_entity = 'MAJ' and coalesce(e2c_int, e2c_text) is not null and e2c_del = 0
						where
							maj_del = 0 ".((!is_null($where))?(" and ".$where):(""))."
						order by
							ins_name, cou_name, sub_name, cat_order
						";
			$result = $this->db->query($qs);

			if($result->num_rows()) {
				foreach ($result->result() as $row) {
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["majName"] = $row->maj_name;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["majProgType"] = $row->maj_prog_type;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["majProgTypeLname"] = $row->szo_lname;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["majUrlName"] = $row->maj_url_name;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["catName"] = $row->cat_name;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["catHighlight"] = $row->cat_highlight;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["e2cInt"] = $row->e2c_int;
					$getAvailablePrograms["ins"][$row->ins_id]["majors"][$row->maj_id]["cat"][$row->cat_id]["e2cText"] = $row->e2c_text;
				}
			}

		}

		return $getAvailablePrograms;
	}

}